use T2Logging;
GO
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_CATALOG = N'T2Logging' 
	 AND SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'Log_Search' 
)
   DROP PROCEDURE dbo.Log_Search
GO
create procedure dbo.Log_Search(
    @PageSize int ,
    @PageNumber int, 
    @SortColumn nVarChar (100) ,
    @SortType nVarChar (4),
    @TotalRows int = null OUTPUT, 
@Title NVarChar(256) = N'%' , 
@MachineName NVarChar(32) = N'%' , 
@AppDomainName NVarChar(512) = N'%' , 
@Message NVarChar(2000) = N'%' , 
	@RowsAffected int = null OUTPUT )
--*************************************************************
--Created By: trooks
--Name: .dbo_Log_Search
--Table: Log
--Database: T2Logging
--Created Date: 02/15/2013 00:00:00
--*************************************************************

as
	Begin Try
        Declare @StartIndex int = (@PageNumber - 1) * @PageSize + 1
        Declare @EndingIndex int = (@PageSize - 1) * @PageNumber + @PageNumber 
        Set @TotalRows = (Select Count(*) from dbo.Log)
        IF @SortType = 'asc'
				BEGIN
					WITH tempLog  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  asc ) AS RowNumber,
		LogID, 
		Priority, 
		Severity, 
		Title, 
		MachineName, 
		AppDomainName, 
		Message, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		ApplicationID, 
		LogCategoryID 
        
		From [dbo].Log 
Where Title Like '%' + @Title + '%'  AND MachineName Like '%' + @MachineName + '%'  AND AppDomainName Like '%' + @AppDomainName + '%'  AND Message Like '%' + @Message + '%'  
		)
                    SELECT	RowNumber, 
		LogID, 
		Priority, 
		Severity, 
		Title, 
		MachineName, 
		AppDomainName, 
		Message, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		ApplicationID, 
		LogCategoryID 
                    FROM	tempLog
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
			ELSE
                BEGIN
					WITH tempLog  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  desc ) AS RowNumber,
		LogID, 
		Priority, 
		Severity, 
		Title, 
		MachineName, 
		AppDomainName, 
		Message, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		ApplicationID, 
		LogCategoryID 
        
		From [dbo].Log 
Where Title Like '%' + @Title + '%'  AND MachineName Like '%' + @MachineName + '%'  AND AppDomainName Like '%' + @AppDomainName + '%'  AND Message Like '%' + @Message + '%'  
		)
                    SELECT	RowNumber, 
		LogID, 
		Priority, 
		Severity, 
		Title, 
		MachineName, 
		AppDomainName, 
		Message, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		ApplicationID, 
		LogCategoryID 
                    FROM	tempLog
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
		Set @RowsAffected = @@ROWCOUNT;
		return 0;	
	END TRY
	BEGIN CATCH
		Set @RowsAffected = 0;
		EXEC dbo.uspLogError;
		return -1;
	END CATCH
	
GO
--GRANT EXECUTE ON [T2Logging].[dbo].[Log_Search] to PORTAL_T2Logging